package repository

import (
	"context"
	"database/sql"
	"errors"
	"strings"
	"time"

	"gitee.com/hp-huiw/family-expense-api/internal/db"
	dbmodel "gitee.com/hp-huiw/family-expense-api/internal/db/model"
	jet "github.com/go-jet/jet/v2/mysql"
	"github.com/go-jet/jet/v2/qrm"
)

type JoinAccountGroupReceiving struct {
	ID                  string    `db:"id"`
	OwnerUsername       string    `db:"owner_username"`
	OwnerFullName       string    `db:"owner_full_name"`
	OwnerAvatarFilename string    `db:"owner_avatar_filename"`
	SenderUsername      string    `db:"sender_username"`
	SenderFullName      string    `db:"sender_full_name"`
	LastUpdated         time.Time `db:"last_updated"`
}

type JoinAccountGroupRequestRepository interface {
	CountReceived(ctx context.Context, receiverUserID string) (int64, error)
	QueryReceiving(ctx context.Context, receiverUserID string, pageSize int64, pageIndex int64) ([]JoinAccountGroupReceiving, error)
	QueryByReceiver(ctx context.Context, id string, receiverID string) (*dbmodel.JoinAccountGroupRequest, error)
	Exists(ctx context.Context, accountGroupID string, senderID string, receiverID string) (bool, error)
	Insert(ctx context.Context, model *dbmodel.JoinAccountGroupRequest) error
	Update(ctx context.Context, accountGroupID string, senderID string, receiverID string, roles []string) error
	DeleteByReceiver(ctx context.Context, db qrm.DB, id string, receiverID string) error
	DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string, userID string) error
}

func NewJoinAccountGroupRequestRepository(db *sql.DB) JoinAccountGroupRequestRepository {
	return &joinAccountGroupRequestRepository{db: db}
}

// CountReceived counts received requests by receiver user id.
func (r *joinAccountGroupRequestRepository) CountReceived(ctx context.Context, receiverUserID string) (int64, error) {
	return db.Count(ctx, r.db, tbJoinRequest, tbJoinRequest.ReceiverID.EQ(jet.String(receiverUserID)))
}

// QueryReceiving queries pending requests for receiver.
func (r *joinAccountGroupRequestRepository) QueryReceiving(ctx context.Context, receiverUserID string, pageSize int64, pageIndex int64) ([]JoinAccountGroupReceiving, error) {
	var query []struct {
		ID                  string    `db:"id"`
		OwnerUsername       string    `db:"owner_username"`
		OwnerFullName       string    `db:"owner_full_name"`
		OwnerAvatarFilename string    `db:"owner_avatar_filename"`
		SenderUsername      string    `db:"sender_username"`
		SenderFullName      string    `db:"sender_full_name"`
		LastUpdated         time.Time `db:"last_updated"`
	}

	owner := tbUser.AS("owner")
	sender := tbUser.AS("sender")

	stmt := jet.
		SELECT(
			tbJoinRequest.ID.AS("id"),
			owner.Username.AS("owner_username"),
			owner.FullName.AS("owner_full_name"),
			owner.AvatarFilename.AS("owner_avatar_filename"),
			sender.Username.AS("sender_username"),
			sender.FullName.AS("sender_full_name"),
			tbJoinRequest.LastUpdated.AS("last_updated"),
		).
		FROM(
			tbJoinRequest.
				INNER_JOIN(owner, owner.ID.EQ(tbJoinRequest.OwnerID)).
				INNER_JOIN(sender, sender.ID.EQ(tbJoinRequest.SenderID)),
		).
		WHERE(
			tbJoinRequest.ReceiverID.EQ(jet.String(receiverUserID)),
		).
		ORDER_BY(
			tbJoinRequest.LastUpdated.DESC(),
		).
		LIMIT(pageSize).
		OFFSET(pageSize * pageIndex)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]JoinAccountGroupReceiving, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryByReceiver queries join account group request by id and receiver id.
func (r *joinAccountGroupRequestRepository) QueryByReceiver(ctx context.Context, id string, receiverID string) (*dbmodel.JoinAccountGroupRequest, error) {
	var model dbmodel.JoinAccountGroupRequest

	stmt := jet.
		SELECT(
			tbJoinRequest.AllColumns,
		).
		FROM(
			tbJoinRequest,
		).
		WHERE(
			tbJoinRequest.ID.EQ(jet.String(id)).
				AND(tbJoinRequest.ReceiverID.EQ(jet.String(receiverID))),
		)

	if err := stmt.QueryContext(ctx, r.db, &model); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return nil, nil
		}

		return nil, err
	}

	return &model, nil
}

// Exists checks if the request exists by account group id, sender id and receiver id.
func (r *joinAccountGroupRequestRepository) Exists(ctx context.Context, accountGroupID string, senderID string, receiverID string) (bool, error) {
	condition := tbJoinRequest.AccountGroupID.EQ(jet.String(accountGroupID)).
		AND(tbJoinRequest.SenderID.EQ(jet.String(senderID))).
		AND(tbJoinRequest.ReceiverID.EQ(jet.String(receiverID)))

	return db.Exists(ctx, r.db, tbJoinRequest, condition)
}

// Insert inserts new join account group request.
func (r *joinAccountGroupRequestRepository) Insert(ctx context.Context, model *dbmodel.JoinAccountGroupRequest) error {
	stmt := tbJoinRequest.
		INSERT().
		MODEL(model)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// Update updates existing join account group request.
func (r *joinAccountGroupRequestRepository) Update(ctx context.Context, accountGroupID string, senderID string, receiverID string, roles []string) error {
	stmt := tbJoinRequest.
		UPDATE().
		SET(
			tbJoinRequest.Roles.SET(jet.String(strings.Join(roles, ","))),
		).
		WHERE(
			tbJoinRequest.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbJoinRequest.SenderID.EQ(jet.String(senderID))).
				AND(tbJoinRequest.ReceiverID.EQ(jet.String(receiverID))),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

func (*joinAccountGroupRequestRepository) DeleteByReceiver(ctx context.Context, db qrm.DB, id string, receiverID string) error {
	stmt := tbJoinRequest.
		DELETE().
		WHERE(
			tbJoinRequest.ID.EQ(jet.String(id)).
				OR(tbJoinRequest.ReceiverID.EQ(jet.String(receiverID))),
		)

	_, err := stmt.ExecContext(ctx, db)

	return err
}

// DeleteWithTx deletes all join account group requests by account group id or user id with transaction.
func (*joinAccountGroupRequestRepository) DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string, userID string) error {
	stmt := tbJoinRequest.
		DELETE().
		WHERE(
			tbJoinRequest.ReceiverID.EQ(jet.String(userID)).
				OR(tbJoinRequest.SenderID.EQ(jet.String(userID))).
				OR(tbJoinRequest.OwnerID.EQ(jet.String(userID))),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

type joinAccountGroupRequestRepository struct {
	db *sql.DB
}
